--    Program:      CAPCMIMP_CE_STATEMENT_HEADERS_INT_ALL.sql
--    Description:  Import CM STATEMENT HEADERS Interface All information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   ANDY MAK
--    Date:         8-MAY-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg		      varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_statement_id			number;
                    		                 		
   cursor cur_1 is
      select * from CRST_CE_STMT_HEADERS_INT_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_1 loop   	
     l_err_msg_tmp := '';
     
     begin
       -- import data
       insert into ce_statement_headers_int_all
       (
  		STATEMENT_NUMBER       ,
  		BANK_ACCOUNT_NUM       ,
  		STATEMENT_DATE         ,
  		BANK_NAME              ,
  		BANK_BRANCH_NAME       ,
  		CONTROL_BEGIN_BALANCE  ,
  		CONTROL_TOTAL_DR       ,
  		CONTROL_TOTAL_CR       ,
  		CONTROL_END_BALANCE    ,
  		CONTROL_DR_LINE_COUNT  ,
  		CONTROL_CR_LINE_COUNT  ,
  		CONTROL_LINE_COUNT     ,
  		RECORD_STATUS_FLAG     ,
  		CURRENCY_CODE          ,
  		CREATED_BY             ,
  		CREATION_DATE          ,
  		ATTRIBUTE_CATEGORY     ,
  		ATTRIBUTE1             ,
  		ATTRIBUTE2             ,
  		ATTRIBUTE3             ,
  		ATTRIBUTE4             ,
  		ATTRIBUTE5             ,
  		ATTRIBUTE6             ,
  		ATTRIBUTE7             ,
  		ATTRIBUTE8             ,
  		ATTRIBUTE9             ,
  		ATTRIBUTE10            ,
  		ATTRIBUTE11            ,
  		ATTRIBUTE12            ,
 		ATTRIBUTE13            ,
  		ATTRIBUTE14            ,
  		ATTRIBUTE15            ,
		LAST_UPDATED_BY	     ,
  		LAST_UPDATE_DATE       ,
  		ORG_ID                 ,
  		CHECK_DIGITS           ,
  		CASHFLOW_BALANCE       ,
  		INT_CALC_BALANCE       ,
  		INTRA_DAY_FLAG          
       )
       values
       (
  		cur.STATEMENT_NUMBER       ,
  		cur.BANK_ACCOUNT_NUM       ,
  		cur.STATEMENT_DATE         ,
  		cur.BANK_NAME              ,
  		cur.BANK_BRANCH_NAME       ,
  		cur.CONTROL_BEGIN_BALANCE  ,
  		cur.CONTROL_TOTAL_DR       ,
  		cur.CONTROL_TOTAL_CR       ,
  		cur.CONTROL_END_BALANCE    ,
  		cur.CONTROL_DR_LINE_COUNT  ,
  		cur.CONTROL_CR_LINE_COUNT  ,
  		cur.CONTROL_LINE_COUNT     ,
  		cur.RECORD_STATUS_FLAG     ,
  		cur.CURRENCY_CODE          ,
  		CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
  		cur.CREATION_DATE          ,
  		cur.ATTRIBUTE_CATEGORY     ,
  		cur.ATTRIBUTE1             ,
  		cur.ATTRIBUTE2             ,
  		cur.ATTRIBUTE3             ,
  		cur.ATTRIBUTE4             ,
  		cur.ATTRIBUTE5             ,
  		cur.ATTRIBUTE6             ,
  		cur.ATTRIBUTE7             ,
  		cur.ATTRIBUTE8             ,
  		cur.ATTRIBUTE9             ,
  		cur.ATTRIBUTE10            ,
  		cur.ATTRIBUTE11            ,
  		cur.ATTRIBUTE12            ,
 		cur.ATTRIBUTE13            ,
  		cur.ATTRIBUTE14            ,
  		cur.ATTRIBUTE15            ,
		CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
  		cur.LAST_UPDATE_DATE       ,
  		CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORG_ID),
  		cur.CHECK_DIGITS           ,
  		cur.CASHFLOW_BALANCE       ,
  		cur.INT_CALC_BALANCE       ,
  		cur.INTRA_DAY_FLAG          
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if (l_err_msg_tmp = '' or l_err_msg_tmp is null) then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   if l_err_msg is null then
      l_err_msg := 'No Error';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPCM_STATEMENT_HEADERS_INT_ALL', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/
exit
